if exists (select 1
          from sysobjects
          where  id = object_id('bd_proc_annualLeave')
          and type in ('P','PC'))
DROP PROCEDURE bd_proc_annualLeave
GO
CREATE PROCEDURE [dbo].[bd_proc_annualLeave]
@selectDeptIds AS VARCHAR (max),
@selectUserIds AS VARCHAR (max),
@selectYear AS VARCHAR (4)

AS
BEGIN
	DECLARE @sqlStr VARCHAR(max)

	--涉及到的表：dt_user、dt_dep、bd_kq_holidays

	--以人为单位，以人、年、月分组求出每个人、每个年月的年假使用量。
	--以人为单位，以人、年 查询出最后一条数据，上面的剩余量，就位当年的剩余量（如果是本年，那自然是目前剩余量）
	create table #userTalbe(
		user_serial numeric null
	)
	--插入需要统计的人
	set @sqlStr = 'insert into #userTalbe select n.user_serial from bd_nianjia n INNER JOIN dt_user u on n.user_serial = u.user_serial where 1=1 ';

	if @selectDeptIds!='' BEGIN
		set @sqlStr = @sqlStr+' and u.user_dep in(' + @selectDeptIds+')'
	END
	if @selectUserIds!='' BEGIN
		set @sqlStr = @sqlStr+' and u.user_serial in(' + @selectUserIds+')'
	END
	set @sqlStr = @sqlStr+' GROUP BY n.user_serial'
	print @sqlStr;
	exec(@sqlStr);
	--返回结果
	select
		a.user_serial userSerial,
		u.user_no as userNo,
		u.user_lname as userName,
		d3.dep_name as deptName3,
		d2.dep_name as deptName2,
		d1.dep_name as deptName1,
		dbo.bd_fn_numToMin(isnull(b.last_time,0)+isnull(b.last_year_time,0)) as fdnj,
		dbo.bd_fn_numToMin(isnull(b.last_time_welfare,0)+isnull(b.last_year_time_welfare,0)) as flnj,
		dbo.bd_fn_numToMin(isnull(c.use_time,0)) as sdts,
		dbo.bd_fn_numToMin(-isnull(m1.total_time,0)) as m1,
		dbo.bd_fn_numToMin(-isnull(m2.total_time,0)) as m2,
		dbo.bd_fn_numToMin(-isnull(m3.total_time,0)) as m3,
		dbo.bd_fn_numToMin(-isnull(m4.total_time,0)) as m4,
		dbo.bd_fn_numToMin(-isnull(m5.total_time,0)) as m5,
		dbo.bd_fn_numToMin(-isnull(m6.total_time,0)) as m6,
		dbo.bd_fn_numToMin(-isnull(m7.total_time,0)) as m7,
		dbo.bd_fn_numToMin(-isnull(m8.total_time,0)) as m8,
		dbo.bd_fn_numToMin(-isnull(m9.total_time,0)) as m9,
		dbo.bd_fn_numToMin(-isnull(m10.total_time,0)) as m10,
		dbo.bd_fn_numToMin(-isnull(m11.total_time,0)) as m11,
		dbo.bd_fn_numToMin(-isnull(m12.total_time,0)) as m12,
		dbo.bd_fn_numToMin(-(isnull(m1.total_time,0)+isnull(m2.total_time,0)+isnull(m3.total_time,0)+isnull(m4.total_time,0)+isnull(m5.total_time,0)+isnull(m6.total_time,0)+isnull(m7.total_time,0)+isnull(m8.total_time,0)+isnull(m9.total_time,0)+isnull(m10.total_time,0)+isnull(m11.total_time,0)+isnull(m12.total_time,0))) as jlsynj,
		dbo.bd_fn_numToMin(isnull(b.remain_time,0)) as synj
from #userTalbe a
	left JOIN (
			SELECT
					user_serial,
					ho_year,
					ho_month,
					SUM ( use_time ) AS total_time
				FROM
					bd_kq_holidays
				WHERE
					ho_year = @selectYear
					AND ho_type = 1
					and ho_month = 1
					and related_result!=0
					and related_id!=0
				GROUP BY
					user_serial,
					ho_year,
					ho_month) m1 on a.user_serial = m1.user_serial
	left JOIN (
			SELECT
					user_serial,
					ho_year,
					ho_month,
					SUM ( use_time ) AS total_time
				FROM
					bd_kq_holidays
				WHERE
					ho_year = @selectYear
					AND ho_type = 1
					and ho_month = 2
					and related_result!=0
					and related_id!=0
				GROUP BY
					user_serial,
					ho_year,
					ho_month) m2 on a.user_serial = m2.user_serial
	left JOIN (
			SELECT
					user_serial,
					ho_year,
					ho_month,
					SUM ( use_time ) AS total_time
				FROM
					bd_kq_holidays
				WHERE
					ho_year = @selectYear
					AND ho_type = 1
					and ho_month = 3
					and related_result!=0
					and related_id!=0
				GROUP BY
					user_serial,
					ho_year,
					ho_month) m3 on a.user_serial = m3.user_serial
	left JOIN (
			SELECT
					user_serial,
					ho_year,
					ho_month,
					SUM ( use_time ) AS total_time
				FROM
					bd_kq_holidays
				WHERE
					ho_year = @selectYear
					AND ho_type = 1
					and ho_month = 4
					and related_result!=0
					and related_id!=0
				GROUP BY
					user_serial,
					ho_year,
					ho_month) m4 on a.user_serial = m4.user_serial
	left JOIN (
			SELECT
					user_serial,
					ho_year,
					ho_month,
					SUM ( use_time ) AS total_time
				FROM
					bd_kq_holidays
				WHERE
					ho_year = @selectYear
					AND ho_type = 1
					and ho_month = 5
					and related_result!=0
					and related_id!=0
				GROUP BY
					user_serial,
					ho_year,
					ho_month) m5 on a.user_serial = m5.user_serial
	left JOIN (
			SELECT
					user_serial,
					ho_year,
					ho_month,
					SUM ( use_time ) AS total_time
				FROM
					bd_kq_holidays
				WHERE
					ho_year = @selectYear
					AND ho_type = 1
					and ho_month = 6
					and related_result!=0
					and related_id!=0
				GROUP BY
					user_serial,
					ho_year,
					ho_month) m6 on a.user_serial = m6.user_serial
	left JOIN (
			SELECT
					user_serial,
					ho_year,
					ho_month,
					SUM ( use_time ) AS total_time
				FROM
					bd_kq_holidays
				WHERE
					ho_year = @selectYear
					AND ho_type = 1
					and ho_month = 7
					and related_result!=0
					and related_id!=0
				GROUP BY
					user_serial,
					ho_year,
					ho_month) m7 on a.user_serial = m7.user_serial
	left JOIN (
			SELECT
					user_serial,
					ho_year,
					ho_month,
					SUM ( use_time ) AS total_time
				FROM
					bd_kq_holidays
				WHERE
					ho_year = @selectYear
					AND ho_type = 1
					and ho_month = 8
					and related_result!=0
					and related_id!=0
				GROUP BY
					user_serial,
					ho_year,
					ho_month) m8 on a.user_serial = m8.user_serial
	left JOIN (
			SELECT
					user_serial,
					ho_year,
					ho_month,
					SUM ( use_time ) AS total_time
				FROM
					bd_kq_holidays
				WHERE
					ho_year = @selectYear
					AND ho_type = 1
					and ho_month = 9
					and related_result!=0
					and related_id!=0
				GROUP BY
					user_serial,
					ho_year,
					ho_month) m9 on a.user_serial = m9.user_serial
	left JOIN (
			SELECT
					user_serial,
					ho_year,
					ho_month,
					SUM ( use_time ) AS total_time
				FROM
					bd_kq_holidays
				WHERE
					ho_year = @selectYear
					AND ho_type = 1
					and ho_month = 10
					and related_result!=0
					and related_id!=0
				GROUP BY
					user_serial,
					ho_year,
					ho_month) m10 on a.user_serial = m10.user_serial
	left JOIN (
			SELECT
					user_serial,
					ho_year,
					ho_month,
					SUM ( use_time ) AS total_time
				FROM
					bd_kq_holidays
				WHERE
					ho_year = @selectYear
					AND ho_type = 1
					and ho_month = 11
					and related_result!=0
					and related_id!=0
				GROUP BY
					user_serial,
					ho_year,
					ho_month) m11 on a.user_serial = m11.user_serial
		left JOIN (
			SELECT
					user_serial,
					ho_year,
					ho_month,
					SUM ( use_time ) AS total_time
				FROM
					bd_kq_holidays
				WHERE
					ho_year = @selectYear
					AND ho_type = 1
					and ho_month = 12
					and related_result!=0
					and related_id!=0
				GROUP BY
					user_serial,
					ho_year,
					ho_month) m12 on a.user_serial = m12.user_serial
		LEFT JOIN (
			SELECT
				*
			FROM
				( SELECT *, row_number () OVER ( partition BY user_serial ORDER BY ho_date DESC ) rowid FROM bd_kq_holidays WHERE ho_year = @selectYear AND ho_type = 1 ) t
			WHERE
			rowid = 1
			) b ON a.user_serial = b.user_serial
		left join (
			SELECT
				*
			FROM
				( SELECT *, row_number () OVER ( partition BY user_serial ORDER BY ho_date DESC ) rowid FROM bd_kq_holidays WHERE ho_year = @selectYear AND ho_type = 9 ) t
			WHERE
			rowid = 1
			) c on  a.user_serial =c.user_serial
		INNER JOIN dt_user u on u.user_serial = a.user_serial
		INNER JOIN dt_dep d1 on u.user_dep = d1.dep_serial
		LEFT JOIN dt_dep d2 on d1.dep_parent = d2.dep_serial
		LEFT JOIN dt_dep d3 on d2.dep_parent = d3.dep_serial;

		drop table #userTable;
END